Part A

1. How many orders were completed in 2018? (Note: We operate in US/Eastern time zone)

2. How many orders were completed in 2018 containing at least 10 units?

NOTE: I used SELECT DISTINCT in the CTE as I noticed there were duplicated records in the line_items table. I removed the duplicates when ingesting the data into Postgres, but the solution below will work if that step was not taken.

3. How many customers have ever purchased a medium sized sweater with a discount?

4. How profitable was our most profitable month?

NOTE: After applying the discount to the customer costs, I rounded down to the nearest cent. If JiffyShirts uses "real" rounding, the following can be used as the last step in the qry:

SELECT
    order_month,
    CAST(
        SUM(
            ROUND((non_discounted_rate * (price + shipping_revenue))::NUMERIC, 2) -
            (supplier_cost + shipping_cost)
        ) 
        AS DECIMAL(16,2)
    ) AS profit
FROM
    order_prices

The profit for the most profitable month (October 2020) would be $63,612.08 with that change applied, a difference of only about 5 dollars.

5. What is the return rate for business vs. non-business customers?

Part B

Customer Segmentation

How should we segment our customers to align different kinds of marketing messaging and offers?

Key customer metrics:

Key findings: